package org.lemon.cla.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.lemon.cla.pojo.ClubApplication;
import org.lemon.cla.vo.ClubApplicationVo; // 导入 ClubApplicationVo
import org.lemon.cla.dto.request.ClubApplicationSearchRequest;

@Mapper
public interface ClubApplicationMapper extends BaseMapper<ClubApplication> {

    // 联查申请人用户表的公共字段片段
    String JOIN_APPLICANT_USER_FIELDS =
            "u1.username AS applicantUsername, " +
                    "u1.avatar_url AS applicantAvatarUrl, " +
                    "u1.real_name AS applicantRealName, " +
                    "u1.phone AS applicantPhone, " +
                    "u1.email AS applicantEmail, " +
                    "u1.role AS applicantRole, " +
                    "u1.is_deleted AS applicantIsDeleted ";

    // 联查审核人用户表的公共字段片段
    String JOIN_REVIEWER_USER_FIELDS =
            "u2.username AS reviewerUsername, " +
                    "u2.avatar_url AS reviewerAvatarUrl, " +
                    "u2.real_name AS reviewerRealName, " +
                    "u2.phone AS reviewerPhone, " +
                    "u2.email AS reviewerEmail, " +
                    "u2.role AS reviewerRole, " +
                    "u2.is_deleted AS reviewerIsDeleted ";

    // 公共JOIN部分
    String COMMON_JOINS =
            "LEFT JOIN user u1 ON ca.user_id = u1.user_id " + // 申请人
                    "LEFT JOIN user u2 ON ca.reviewer_id = u2.user_id "; // 审核人


    /**
     * 根据申请ID获取社团申请详情，包含申请人和审核人信息。
     * @param applicationId 申请ID
     * @return 社团申请视图对象
     */
    @Select("SELECT ca.*, " +
            JOIN_APPLICANT_USER_FIELDS + ", " +
            JOIN_REVIEWER_USER_FIELDS +
            "FROM club_application ca " +
            COMMON_JOINS +
            "WHERE ca.application_id = #{applicationId}")
    ClubApplicationVo selectApplicationVoById(@Param("applicationId") Long applicationId);

    /**
     * 分页查询所有社团申请，包含申请人和审核人信息。
     * @param page 分页对象
     * @return 分页社团申请视图对象列表
     */
    @Select("SELECT ca.*, " +
            JOIN_APPLICANT_USER_FIELDS + ", " +
            JOIN_REVIEWER_USER_FIELDS +
            "FROM club_application ca " +
            COMMON_JOINS +
            "ORDER BY ca.application_time DESC")
    IPage<ClubApplicationVo> selectApplicationVoPage(IPage<ClubApplicationVo> page);

    /**
     * 根据多个条件模糊查询社团申请，包含申请人和审核人信息。
     *
     * @param page 分页对象
     * @param req 查询请求DTO
     * @return 分页社团申请视图对象列表
     */
    @Select("<script>" +
            "SELECT ca.*, " +
            JOIN_APPLICANT_USER_FIELDS + ", " +
            JOIN_REVIEWER_USER_FIELDS +
            "FROM club_application ca " +
            COMMON_JOINS +
            "<where>" +
            "  <if test='req.userId != null'>" +
            "    AND ca.user_id = #{req.userId}" +
            "  </if>" +
            "  <if test='req.clubId != null'>" +
            "    AND ca.club_id = #{req.clubId}" +
            "  </if>" +
            "  <if test='req.reason != null and req.reason != \"\"'>" +
            "    AND ca.reason LIKE CONCAT('%', #{req.reason}, '%')" +
            "  </if>" +
            "  <if test='req.status != null'>" +
            "    AND ca.status = #{req.status}" +
            "  </if>" +
            "  <if test='req.applicationTimeStart != null'>" +
            "    AND ca.application_time &gt;= #{req.applicationTimeStart}" +
            "  </if>" +
            "  <if test='req.applicationTimeEnd != null'>" +
            "    AND ca.application_time &lt;= #{req.applicationTimeEnd}" +
            "  </if>" +
            "  <if test='req.reviewTimeStart != null'>" +
            "    AND ca.review_time &gt;= #{req.reviewTimeStart}" +
            "  </if>" +
            "  <if test='req.reviewTimeEnd != null'>" +
            "    AND ca.review_time &lt;= #{req.reviewTimeEnd}" +
            "  </if>" +
            "  <if test='req.reviewerId != null'>" +
            "    AND ca.reviewer_id = #{req.reviewerId}" +
            "  </if>" +
            "</where>" +
            "ORDER BY ca.application_time DESC" +
            "</script>")
    IPage<ClubApplicationVo> selectApplicationVoByMultiConditions(
            IPage<ClubApplicationVo> page,
            @Param("req") ClubApplicationSearchRequest request
    );
}